package com.bwda.dsrs.filecenter.service.impl;

import com.bwda.dsrs.base.util.AesEncryptPaddingUtil;
import com.bwda.dsrs.filecenter.domain.po.dbinfo.DbInfoPo;
import com.bwda.dsrs.filecenter.domain.po.dbscanresult.DbScanResultPo;
import com.bwda.dsrs.filecenter.service.IRemoteService;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;


import java.sql.*;
import java.util.Date;
import java.util.*;
/**
 * @author Administrator
 */
@Service
public class PgsqlRemoteServiceImpl implements IRemoteService {

    @Value("${aes_key}")
    private String aesKey;
    private final Logger logger = LoggerFactory.getLogger(PgsqlRemoteServiceImpl.class);
    @Override
    public Connection login(DbInfoPo dbInfoPo) throws Exception {
        StringBuffer url = new StringBuffer();
        url.append("jdbc:postgresql://").append(dbInfoPo.getDbIp());
        url.append(":").append(dbInfoPo.getDbPort());
        url.append("/").append(dbInfoPo.getServiceName());
        String driverName = "org.postgresql.Driver";
        Properties conProps = new Properties();
        conProps.put("user", dbInfoPo.getDbAccount());
        String pwdDecryp = AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(),aesKey);
        logger.info("ip:{}，name:{}",dbInfoPo.getDbIp(),dbInfoPo.getServiceName());
        conProps.put("password",pwdDecryp);
        conProps.put("connectTimeout","10");
        Class.forName(driverName);
        return DriverManager.getConnection(url.toString(),conProps);
    }
    @Override
    public List<Map<String,Object>> getData(Connection connection,String tableName,String columnName,Integer rowNum,Integer sumRowNum) throws Exception {
        String sql;
        if(StringUtils.isBlank(columnName)){
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//             sql = "select "+"\""+columnName+"\""+" from "+tableName+" limit "+rowNum+" offset "+(sumRowNum-rowNum);//最新数据
                sql = "select * from "+tableName+" limit "+rowNum;
            }
            else {
                sql = "select * from "+tableName;
            }
        }
        else {
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//             sql = "select "+"\""+columnName+"\""+" from "+tableName+" limit "+rowNum+" offset "+(sumRowNum-rowNum);//最新数据
                sql = "select "+"\""+columnName+"\""+" from "+tableName+" limit "+rowNum;
            }
            else {
                sql = "select "+"\""+columnName+"\""+" from "+tableName;
            }
        }
        logger.info("will run sql = {}",sql);
//        List<Object> res = new ArrayList<>();
        List<Map<String,Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
//        while (rs.next()) {
//            for (int i = 1; i <= columnCount; i++) {
//                res.add( rs.getObject(i));
//            }
//        }
        while (rs.next()) {
            Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));//获取键名及值
            }
            res.add(rowData);
        }
        rs.close();
        stmt.close();
        return res;
//        return JSONObject.parseArray(JSON.toJSONString(res),DbInfoPo.class);
    }
    @Override
    public List<String> getAllDBList(Connection connection,DbInfoPo dbInfoPo)throws Exception{
        String sql = "select datname as dbname from pg_database where datname not in('template0','template1')";
        logger.info("getAllDBList will run sql = {}",sql);
        List<String> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        while (rs.next()) {
            res.add(rs.getString(1));
        }
        rs.close();
        stmt.close();
        return res;
    }

    @Override
    public List<String> getAllTableList(Connection connection,String dbName) throws Exception{
        List<String> stringList = new ArrayList<>();
        String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null, null, "%", types);
        while (rs.next()) {
            String schemaName =  rs.getString(2);
            String tableName = rs.getString("TABLE_NAME"); // 表名
            stringList.add(schemaName.concat(".").concat(tableName));
        }
        rs.close();
        return stringList;
    }
    @Override
    public List<DbScanResultPo> getTableColumns(Connection connection,String tableName) throws Exception{
        ResultSet rs = connection.getMetaData().getColumns(null, tableName.split("\\.")[0], tableName.split("\\.")[1], "%");
        List<DbScanResultPo> dbScanResultPos = new ArrayList<>();
        while (rs.next()) {
            DbScanResultPo dbScanResultPo = new DbScanResultPo();
            dbScanResultPo.setWordIds("");
            dbScanResultPo.setScanNums(0);
            dbScanResultPo.setMatchNums(0);
            dbScanResultPo.setCreateTime(new Date());
            dbScanResultPo.setUpdateTime(new Date());
            dbScanResultPo.setRemark("");
            dbScanResultPo.setDelFlag(0);
//            dbScanResultPo.setTableRemark(rs.getString("TABLE_SCHEM").toLowerCase());
//            dbScanResultPo.setTableName(rs.getString("TABLE_NAME").toLowerCase());
            dbScanResultPo.setTableName(tableName.toLowerCase());
            dbScanResultPo.setColumnRemark(StringUtils.isEmpty(rs.getString("REMARKS"))?"":rs.getString("REMARKS").toLowerCase());
            dbScanResultPo.setColumnName(rs.getString("COLUMN_NAME").toLowerCase());
            dbScanResultPo.setColumnType(rs.getString("TYPE_NAME").toLowerCase());
            dbScanResultPos.add(dbScanResultPo);
//            String tableSchemaName = rs.getString("TABLE_SCHEM");// 表的架构（可能为空）
//            String tableName_ = rs.getString("TABLE_NAME");// 表名
//            String columnName = rs.getString("COLUMN_NAME");// 列名
//            System.err.println(tableSchemaName+'-'+tableName+'-'+columnName);
        }
        rs.close();
        return dbScanResultPos;
    }

    @Override
    public Integer countRowNum(Connection connection, String tableName) throws Exception {
        String sql = "select count(1) as rowcount from "+tableName;
        logger.info("countRowNum will run sql = {}",sql);
        List<Map<String,Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs=  stmt.executeQuery(sql);
        int count = 0;
        while (rs.next()){
            count = rs.getInt(1);
        }
        rs.close();
        stmt.close();
        return count;
    }
    @Override
    public Integer countTable(Connection connection,String dbName) throws Exception{
        String[] types = { "TABLE" };
        ResultSet rs = connection.getMetaData().getTables(null, null, "%", types);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }
    @Override
    public Integer countView(Connection connection) throws Exception{
        String[] types = { "VIEW" };
        ResultSet rs = connection.getMetaData().getTables(null, null, "%", types);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }
    @Override
    public Integer countFunction(Connection connection) throws Exception{
        ResultSet rs = connection.getMetaData().getFunctions(null, null, "%");
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }

    @Override
    public Integer countSequence(Connection connection) throws Exception{
        return null;
    }

    @Override
    public List<Object> getDataBases(DbInfoPo dbInfoPo) throws Exception {
        return null;
    }
}
